package com.deneme.ozveri.database;
import java.util.ArrayList;
import org.json.JSONArray;
import org.json.JSONObject;
import com.deneme.ozveri.objects.MenuItem;
import com.deneme.ozveri.objects.MenuOrderItem;
import com.deneme.ozveri.objects.MenuOrderItemExtras;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {

	private static final String DATABASE_NAME ="OzveriMenu";
	private static final String TABLE_MENU_MAIN ="MenuMain";
	private static final String TABLE_MENU_SUB ="MenuSub";
	private static final String TABLE_MENU_ITEM ="MenuItem";
	private static final String TABLE_MENU_ITEM_EXTRA="MenuItemExtra";


	public static final String MENU_MAIN_ID = "Main_Id";
	public static final String MENU_MAIN_NAME = "Main_Name";

	public static final String MENU_SUB_ID = "Sub_Id";
	public static final String MENU_SUB_NAME = "Sub_Name";

	public static final String MENU_ITEM_ID = "Item_Id";
	public static final String MENU_ITEM_NAME = "Item_Name";
	public static final String MENU_ITEM_PRICE = "Item_Price";
	public static final String MENU_ITEM_DESCRIPTION = "Item_Desc";
	public static final String MENU_ITEM_SHORTDESCRIPTION = "Item_Sdesc";
	
	public static final String MENU_ITEM_EXTRA_ID = "extra_id";
	public static final String MENU_ITEM_EXTRA_NAME = "extra_name";
	public static final String MENU_ITEM_EXTRA_GROUPID = "extra_gid";
	public static final String MENU_ITEM_EXTRA_GROUPNAME = "extra_gnm";
	public static final String MENU_ITEM_EXTRA_PRICE = "extra_prc";
	public static final String MENU_ITEM_EXTRA_TYPE = "extra_type";



	public DBHandler(Context context) {
		super(context, DATABASE_NAME, null, 10/*databaseTypeInit*/);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE "+TABLE_MENU_MAIN+" ("+MENU_MAIN_ID+ " INTEGER PRIMARY KEY , "+MENU_MAIN_NAME+ " TEXT)");

		db.execSQL("CREATE TABLE "+TABLE_MENU_SUB+"("+
				MENU_SUB_ID +" INTEGER PRIMARY KEY, "+
				MENU_SUB_NAME+" TEXT, "+ 
				MENU_MAIN_ID+" INTEGER NOT NULL " +
				",FOREIGN KEY ("+MENU_MAIN_ID+") REFERENCES "+TABLE_MENU_MAIN+" ("+MENU_MAIN_ID+"));");

		db.execSQL("CREATE TABLE "+TABLE_MENU_ITEM+"("+
				MENU_ITEM_ID +" INTEGER PRIMARY KEY, "+
				MENU_ITEM_NAME + " TEXT, "+ 
				MENU_ITEM_DESCRIPTION + " TEXT, "+ 
				MENU_ITEM_SHORTDESCRIPTION + " TEXT, "+ 
				MENU_ITEM_PRICE + " INTEGER, "+ 
				MENU_SUB_ID+" INTEGER NOT NULL " +
				",FOREIGN KEY ("+MENU_SUB_ID+") REFERENCES "+TABLE_MENU_SUB+" ("+MENU_SUB_ID+"));");
		
		db.execSQL("CREATE TABLE "+TABLE_MENU_ITEM_EXTRA+"("+
				MENU_ITEM_EXTRA_ID +" INTEGER PRIMARY KEY, "+
				MENU_ITEM_EXTRA_NAME + " TEXT, "+ 
				MENU_ITEM_EXTRA_GROUPNAME + " TEXT, "+ 
				MENU_ITEM_EXTRA_GROUPID + " INTEGER, "+ 
				MENU_ITEM_EXTRA_PRICE + " INTEGER, "+ 
				MENU_ITEM_EXTRA_TYPE + " INTEGER, "+ 
				MENU_ITEM_ID+" INTEGER NOT NULL " +
				",FOREIGN KEY ("+MENU_ITEM_ID+") REFERENCES "+TABLE_MENU_ITEM+" ("+MENU_ITEM_ID+"));");
		

		//TODO need to create Indexes and Item Detail Table


	}

	@Override	
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_MENU_MAIN);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_MENU_SUB);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_MENU_ITEM);
		db.execSQL("DROP TABLE IF EXISTS "+TABLE_MENU_ITEM_EXTRA);
		  onCreate(db);
	}

//TODO THERE IS NO NULL CHECK DAMN IT
	public void addNewMainMenu(JSONObject item){
		SQLiteDatabase db=this.getWritableDatabase();
		try {
			db.beginTransaction();
			ContentValues cv=new ContentValues();
			cv.put(MENU_MAIN_ID, item.getInt("id"));
			cv.put(MENU_MAIN_NAME, item.getString("nm"));
			db.insert(TABLE_MENU_MAIN, null, cv);
			
			

			JSONArray subItems = item.getJSONArray("sub");
			for (int i = 0; i < subItems.length(); i++) {

				JSONObject currSub = subItems.getJSONObject(i);
				cv=new ContentValues();
				cv.put(MENU_SUB_ID, currSub.getInt("id"));
				cv.put(MENU_SUB_NAME,currSub.getString("nm"));
				cv.put(MENU_MAIN_ID, item.getInt("id"));			
				db.insert(TABLE_MENU_SUB, null, cv);


				JSONArray menuItems = currSub.getJSONArray("itm");
				for (int j = 0; j < menuItems.length(); j++) {
					JSONObject currItem = menuItems.getJSONObject(j);
					cv=new ContentValues();
					cv.put(MENU_ITEM_ID, currItem.getInt("id"));
					cv.put(MENU_ITEM_NAME,currItem.getString("nm"));
					cv.put(MENU_ITEM_DESCRIPTION,currItem.getString("desc"));
					cv.put(MENU_ITEM_SHORTDESCRIPTION,currItem.getString("sdesc"));
					cv.put(MENU_ITEM_PRICE,currItem.getInt("prc"));
					cv.put(MENU_SUB_ID, currSub.getInt("id"));			
					
					db.insert(TABLE_MENU_ITEM, null, cv);
					if (currItem.has("ext")) {
						JSONArray itemExtra = currItem.getJSONArray("ext");
						for (int k = 0; k < itemExtra.length(); k++) {
							JSONObject currExtra = itemExtra.getJSONObject(k);
							cv=new ContentValues();
							cv.put(MENU_ITEM_EXTRA_ID, currExtra.getInt("id"));
							cv.put(MENU_ITEM_EXTRA_NAME,currExtra.getString("nm"));
							cv.put(MENU_ITEM_EXTRA_GROUPID,currExtra.getInt("gid"));
							if (currExtra.getInt("gid")== 0) {
								cv.put(MENU_ITEM_EXTRA_GROUPNAME,"Diger");
							}
							else
							{
								cv.put(MENU_ITEM_EXTRA_GROUPNAME,currExtra.getString("gnm"));
							}
						
							cv.put(MENU_ITEM_EXTRA_PRICE,currExtra.getInt("prc"));
							cv.put(MENU_ITEM_EXTRA_TYPE,currExtra.getInt("typ"));
							cv.put(MENU_ITEM_ID, currItem.getInt("id"));
							db.insert(TABLE_MENU_ITEM_EXTRA, null, cv);

						}
					}


				}

			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.w("DBHandler", "DATABASE TRANSACTION ERROR WHILE INITIALIZING FROM JSON "+e.getMessage());
		} finally{
			db.endTransaction();
		}
		db.close();
	}

	/**
	 * Database query for Main Menu List
	 * @return ArrayList of MenuItem Objects
	 */	
	public ArrayList<MenuItem> getMainMenuItems() {


		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MENU_MAIN, null);
		ArrayList<MenuItem> MainMenuList = new ArrayList<MenuItem>();


		if (c.moveToFirst()) {

			do {
				MenuItem newItem = new MenuItem();
				newItem.setMenuId(c.getInt(c.getColumnIndex(MENU_MAIN_ID)));
				newItem.setMenuName(c.getString(c.getColumnIndex(MENU_MAIN_NAME)));

				MainMenuList.add(newItem);
			} while (c.moveToNext());
			return  MainMenuList;
		} 
		else
		{
			c.close();
			db.close();
			return null;
		}

	}
	
	
	public ArrayList<MenuItem> getSubMenuItems(int MainMenuId)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MENU_SUB + " WHERE " + MENU_MAIN_ID + " = "+ MainMenuId, null);
		ArrayList<MenuItem> SubMenuList = new ArrayList<MenuItem>();


		if (c.moveToFirst()) {

			do {
				MenuItem newItem = new MenuItem();
				newItem.setMenuId(c.getInt(c.getColumnIndex(MENU_SUB_ID)));
				newItem.setMenuName(c.getString(c.getColumnIndex(MENU_SUB_NAME)));

				SubMenuList.add(newItem);
			} while (c.moveToNext());
			return  SubMenuList;
		} 
		else
		{
			c.close();
			db.close();
			return null;
		}
	}
	
	public ArrayList<MenuOrderItem> getOrderItems(int SubMenuId)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MENU_ITEM + " WHERE " + MENU_SUB_ID + " = "+ SubMenuId, null);
		ArrayList<MenuOrderItem> itemList = new ArrayList<MenuOrderItem>();


		if (c.moveToFirst()) {

			do {
				MenuOrderItem newItem = new MenuOrderItem();				
				newItem.setOrderId(c.getInt(c.getColumnIndex(MENU_ITEM_ID)));
				newItem.setOrderPrice(c.getInt(c.getColumnIndex(MENU_ITEM_PRICE)));
				newItem.setOrderShortDescription(c.getString(c.getColumnIndex(MENU_ITEM_SHORTDESCRIPTION)));
				newItem.setOrderName(c.getString(c.getColumnIndex(MENU_ITEM_NAME)));
				newItem.setOrderDescription(c.getString(c.getColumnIndex(MENU_ITEM_DESCRIPTION)));
				newItem.setExtras(getExtras(newItem.getOrderId()));


				itemList.add(newItem);
			} while (c.moveToNext());
			return  itemList;
		} 
		else
		{
			c.close();
			db.close();
			return null;
		}
	}
	
	
	
	public MenuOrderItem getOrderItem(int ItemId)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MENU_ITEM + " WHERE " + MENU_ITEM_ID + " = "+ ItemId, null);
		MenuOrderItem newItem = new MenuOrderItem();


		if (c.moveToFirst()) {

			do {
								
				newItem.setOrderId(c.getInt(c.getColumnIndex(MENU_ITEM_ID)));
				newItem.setOrderPrice(c.getInt(c.getColumnIndex(MENU_ITEM_PRICE)));
				newItem.setOrderShortDescription(c.getString(c.getColumnIndex(MENU_ITEM_SHORTDESCRIPTION)));
				newItem.setOrderName(c.getString(c.getColumnIndex(MENU_ITEM_NAME)));
				newItem.setOrderDescription(c.getString(c.getColumnIndex(MENU_ITEM_DESCRIPTION)));
				newItem.setExtras(getExtras(newItem.getOrderId()));


				
			} while (c.moveToNext());
			return  newItem;
		} 
		else
		{
			c.close();
			db.close();
			return null;
		}
	}
	
	public ArrayList<MenuOrderItemExtras> getExtras(int ItemId)
	{
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MENU_ITEM_EXTRA + " WHERE " + MENU_ITEM_ID + " = "+ ItemId, null);
		ArrayList<MenuOrderItemExtras> extraList = new ArrayList<MenuOrderItemExtras>();

		

		if (c.moveToFirst()) {

			do {
				MenuOrderItemExtras newItem = new MenuOrderItemExtras();				
				newItem.setId(c.getInt(c.getColumnIndex(MENU_ITEM_EXTRA_ID)));
				newItem.setGroupId(c.getInt(c.getColumnIndex(MENU_ITEM_EXTRA_GROUPID)));
				newItem.setPrice(c.getInt(c.getColumnIndex(MENU_ITEM_EXTRA_PRICE)));
				newItem.setQuantityType(c.getInt(c.getColumnIndex(MENU_ITEM_EXTRA_TYPE)));
				newItem.setName(c.getString(c.getColumnIndex(MENU_ITEM_EXTRA_NAME)));
				newItem.setGroupName(c.getString(c.getColumnIndex(MENU_ITEM_EXTRA_GROUPNAME)));


				extraList.add(newItem);
			} while (c.moveToNext());
			return  extraList;
		} 
		else
		{
			c.close();
			db.close();
			return null;
		}
	}



}
